CREATE TABLE [dbo].[Ping](
	[PingId] [bigint] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NOT NULL,
	[DatePing] [datetime] NOT NULL,
	[Latitude] [float] NOT NULL,
	[Longitude] [float] NOT NULL,
 CONSTRAINT [PK_Ping] PRIMARY KEY CLUSTERED 
(
	[PingId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ping]  WITH CHECK ADD  CONSTRAINT [FK_Ping_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Ping] CHECK CONSTRAINT [FK_Ping_User]
GO
CREATE TABLE [dbo].[PromotionCheckinRequests](
	[PromotionId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
	[DateRequest] [datetime] NOT NULL,
	[Latitude] [float] NOT NULL,
	[Longitude] [float] NOT NULL
 CONSTRAINT [PK_PromotionCheckinRequests] PRIMARY KEY CLUSTERED 
(
	[PromotionId] ASC,
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PromotionCheckinRequests]  WITH CHECK ADD  CONSTRAINT [FK_PromotionCheckinRequests_Promotion] FOREIGN KEY([PromotionId])
REFERENCES [dbo].[Promotion] ([PromotionId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PromotionCheckinRequests] CHECK CONSTRAINT [FK_PromotionCheckinRequests_Promotion]
GO
ALTER TABLE [dbo].[PromotionCheckinRequests]  WITH CHECK ADD  CONSTRAINT [FK_PromotionCheckinRequests_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PromotionCheckinRequests] CHECK CONSTRAINT [FK_PromotionCheckinRequests_User]
GO
DROP PROCEDURE spAPI_Promotion_GetCheckins
GO
ALTER PROCEDURE [dbo].[spAPI_Promotion_Push]
-- Use in other SP!!!
-- Not for call directly
(
	@UserId INT,
	@Latitude FLOAT,
	@Longitude FLOAT,
	
	@Result XML OUT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	CREATE TABLE #tmpPromotions
	(
		PromotionId INT,
		RatePerPush MONEY
	)
		
	BEGIN TRANSACTION
			INSERT INTO #tmpPromotions
			(PromotionId, RatePerPush)
			SELECT Promotion.PromotionId, Promotion.RatePerPush
			FROM Promotion (NOLOCK) LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
						LEFT JOIN ProximityRadius (NOLOCK) ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
						LEFT JOIN BlockBusiness (NOLOCK) AS bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
						LEFT JOIN BlockPromotion (NOLOCK) AS bp ON bp.PromotionId = Promotion.PromotionId AND bp.UserId = @UserId
			WHERE 0=0
				--and (NOT EXISTS(SELECT 1 FROM PromotionPush WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = Promotion.PromotionId)))
				AND (dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280))
				--AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) >= CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280))		
				AND (Promotion.Deleted = 0)
				AND (Promotion.Ready = 1)
				AND (Promotion.StatusCode = 'ACT')
				AND (bb.UserId IS NULL)
				AND (bp.UserId IS NULL)
				AND (NOT EXISTS(SELECT 1 
								FROM PromotionCategory (NOLOCK) AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
								WHERE pc.PromotionId = Promotion.PromotionId)
					)
				AND (Business.IsActive = 1)
				
			SELECT @Result = (SELECT Promotion.PromotionId, Promotion.PromotionText, Business.BusinessId, Business.BusinessName,
								STR(dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
								CONVERT(VARCHAR(20), ISNULL(vwReview.AvgRating, 0)) + ',' + 
									CASE WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 0 AND 2 THEN 'Y' 
										WHEN ISNULL(vwReview.AvgRating, 0) BETWEEN 3 AND 4 THEN 'G' 
									ELSE 'R' END AS Rating,
								ISNULL(vwReview.CountReviews, 0) AS Reviews, 15 AS PriceRange,
								Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
								Business.Latitude, Business.Longitude,
								Promotion.PromotionText, Business.[Description], 
								
								Business.BusinessPhone, Business.CellPhone, 
								Business.TollFreePhone, Business.Web,
								
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
									FROM BusinessCategory (NOLOCK) LEFT JOIN Category (NOLOCK) ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day] (NOLOCK)
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours
							FROM #tmpPromotions INNER JOIN Promotion (NOLOCK) ON #tmpPromotions.PromotionId = Promotion.PromotionId
											LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
											LEFT JOIN (SELECT BusinessId, 
													COUNT(*) AS CountReviews, AVG(CONVERT(FLOAT, Rating)) AS AvgRating 
													FROM BusinessReview (NOLOCK)
													GROUP BY BusinessId) AS vwReview ON Business.BusinessId = vwReview.BusinessId
							FOR XML PATH ('Promotion'), ROOT('Promotions'))
							
			INSERT INTO PromotionPush
			(PromotionId, UserId, DatePush, Latitude, Longitude, RatePerPush)
			SELECT PromotionId, @UserId, GETUTCDATE(), @Latitude, @Longitude, RatePerPush
			FROM #tmpPromotions
			WHERE (NOT EXISTS(SELECT 1 FROM PromotionPush WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = #tmpPromotions.PromotionId)))
			
			UPDATE Promotion
			SET StatusCode = 'COM'
			FROM #tmpPromotions LEFT JOIN (SELECT PromotionId, SUM(RatePerPush) AS UsedBudget
											FROM PromotionPush
											GROUP BY PromotionId) AS vwPush ON #tmpPromotions.PromotionId = vwPush.PromotionId
			WHERE (Promotion.PromotionId = #tmpPromotions.PromotionId)
				AND (Promotion.CampaignBudget - ISNULL(vwPush.UsedBudget, 0) < Promotion.RatePerPush)
	COMMIT TRANSACTION
		
	DROP TABLE #tmpPromotions
END
GO
ALTER PROCEDURE [dbo].[spAPI_User_Message_GetList]
-- Use in other SP!!!
-- Not for call directly
(
	@UserId INT,
	@Result XML OUT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @Login VARCHAR(50)
		
	SELECT @Login = [Login]
	FROM [User]
	WHERE UserId = @UserId
		
	CREATE TABLE #tmpMessages
	(
		MessageId INT
	)
		
	BEGIN TRANSACTION
			INSERT INTO #tmpMessages
			(MessageId)
			SELECT MessageId
			FROM MessageCenter (NOLOCK)
			WHERE (ToUserLogin = @Login)
				AND (FlagDeleteByReceiver = 0)
				AND (FlagReceived = 0)
			
			UPDATE MessageCenter
			SET FlagReceived = 1, DateReceive = GETUTCDATE()
			FROM #tmpMessages
			WHERE #tmpMessages.MessageId = MessageCenter.MessageId
			
			SELECT @Result =
				(SELECT
					Id = MessageCenter.MessageId
					,Title = MessageCenter.[Subject]
					,[Text] = MessageCenter.Body
					,Sender = coalesce(MessageCenter.FromUserLogin, MessageCenter.FromSystemLogin)
				FROM #tmpMessages INNER JOIN MessageCenter (NOLOCK) ON #tmpMessages.MessageId = MessageCenter.MessageId
				ORDER BY CreatedDate DESC
				FOR XML PATH ('Message'))
	COMMIT TRANSACTION
		
	DROP TABLE #tmpMessages
	 
END
GO
CREATE PROCEDURE spAPI_User_Checkin_Offer
-- Use in other SP!!!
-- Not for call directly
(
	@UserId INT,
	@Latitude FLOAT,
	@Longitude FLOAT,
	
	@Result XML OUT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	/*DECLARE LatitudePast FLOAT, LongitudePast FLOAT
	
	SELECT LatitudePast = Ping.Latitude, LongitudePast = Ping.Longitude
	FROM (SELECT UserId, MAX(DatePing) AS DatePing
		FROM Ping
		WHERE (UserId = @UserId)
			AND (DatePing <= DATEADD(MINUTE, -5, GETUTCDATE())
		GROUP BY UserId) AS vwDate INNER JOIN Ping ON (vwDate.UserId = Ping.UserId) AND (vwDate.DatePing = Ping.DatePing)*/
	
	CREATE TABLE #tmpPromotions
	(
		PromotionId INT
	)
		
	BEGIN TRANSACTION
			INSERT INTO #tmpPromotions
			(PromotionId)
			SELECT Promotion.PromotionId
			FROM Promotion (NOLOCK) LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
						LEFT JOIN ProximityRadius (NOLOCK) ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
						LEFT JOIN BlockBusiness (NOLOCK) AS bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
						LEFT JOIN BlockPromotion (NOLOCK) AS bp ON bp.PromotionId = Promotion.PromotionId AND bp.UserId = @UserId
			WHERE (EXISTS(SELECT 1 FROM PromotionPush WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = Promotion.PromotionId)))
				AND (NOT EXISTS(SELECT 1 FROM PromotionCheckinRequests WHERE (PromotionCheckinRequests.UserId = @UserId) AND (PromotionCheckinRequests.PromotionId = Promotion.PromotionId)))
				AND (dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, 100) / 5280)
				AND (Promotion.Deleted = 0)
				AND (Promotion.Ready = 1)
				AND (Promotion.StatusCode = 'ACT')
				AND (bb.UserId IS NULL)
				AND (bp.UserId IS NULL)
				AND (NOT EXISTS(SELECT 1 
								FROM PromotionCategory (NOLOCK) AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
								WHERE pc.PromotionId = Promotion.PromotionId)
					)
				AND (Business.IsActive = 1)
				
			SELECT @Result = (SELECT CheckinText = 'Check In and leave your feedback to out restaurant',
								Promotion.BusinessId, Business.BusinessName
			FROM #tmpPromotions INNER JOIN Promotion (NOLOCK) ON #tmpPromotions.PromotionId = Promotion.PromotionId
								LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
			FOR XML PATH ('CheckIn'), ROOT('CheckIns'))	
				
			INSERT INTO PromotionCheckinRequests
			(PromotionId, UserId, DateRequest, Latitude, Longitude)
			SELECT PromotionId, @UserId, GETUTCDATE(), @Latitude, @Longitude
			FROM #tmpPromotions
			WHERE (NOT EXISTS(SELECT 1 FROM PromotionCheckinRequests WHERE (PromotionCheckinRequests.UserId = @UserId) AND (PromotionCheckinRequests.PromotionId = #tmpPromotions.PromotionId)))
	COMMIT TRANSACTION
		
	DROP TABLE #tmpPromotions
END
GO
CREATE PROCEDURE spAPI_User_Ping
(
	@Token VARCHAR(255),
	@Latitude FLOAT,
	@Longitude FLOAT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	DECLARE @IdResult INT
	DECLARE @UserId INT
	DECLARE @Result XML, @ResultPush XML, @ResultOffer XML, @ResultMessage XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	BEGIN
		BEGIN TRANSACTION
			EXEC spAPI_Promotion_Push @UserId = @UserId, @Latitude = @Latitude, @Longitude = @Longitude, @Result = @ResultPush OUT
							
			EXEC spAPI_User_Checkin_Offer @UserId = @UserId, @Latitude = @Latitude, @Longitude = @Longitude, @Result = @ResultOffer OUT
			
			EXEC spAPI_User_Message_GetList @UserId = @UserId, @Result = @ResultMessage OUT
							
			INSERT INTO Ping
			(UserId, DatePing, Latitude, Longitude)
			VALUES (@UserId, GETUTCDATE(), @Latitude, @Longitude)				
			
			SELECT @Result = (SELECT @ResultPush, @ResultOffer, @ResultMessage
								FOR XML PATH(''))
		COMMIT TRANSACTION
	END
	
	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END	
GO